﻿/*
	Split function, created by Jens Suessmeyer, also available on
	http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput

	Split function to parse and retrieve values from a delimited string
	in a tabular format.
	
	Input Parameters:	@String		= Delimited string e.g. 1;2;3
						@Delimter	= Delimiter which separates the values
	Output			:	Table containing two columns (OccurenceId, running value) and SplitValue (the value)
*/

CREATE FUNCTION dbo.Split
(
@String VARCHAR(MAX),
@Delimiter VARCHAR(5)
)
RETURNS @SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @SplitLength INT


WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN
0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)


INSERT INTO @SplittedValues
SELECT SUBSTRING(@String,1,@SplitLength)


SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0
THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END
RETURN
END

